HIPAA-Compliant Database Wrapper API

1. Executive Summary

The HIPAA-Compliant Database Wrapper (HCDW) is a core component of the Login.Health platform, providing developers with a secure and simplified approach to handling protected health information (PHI). This document outlines the design, implementation plan, and technical specifications for creating a database abstraction layer that ensures HIPAA compliance while delivering an exceptional developer experience.

The HCDW will enable developers to focus on building innovative healthcare applications without the significant overhead of implementing complex compliance mechanisms. By handling encryption, access controls, audit logging, and other HIPAA requirements transparently, Login.Health can accelerate the development of healthcare solutions while reducing compliance risks.

2. Project Vision

2.1 Purpose

To create a database wrapper that abstracts away the complexities of HIPAA compliance while providing a developer-friendly interface for storing and retrieving healthcare data.

2.2 Key Objectives

• Create a PostgreSQL database wrapper that enforces HIPAA compliance
• Provide a simple, intuitive API for developers
• Implement transparent field-level encryption for PHI
• Ensure comprehensive audit logging
• Support flexible access controls
• Deliver excellent performance with minimal overhead
• Create comprehensive documentation and developer resources

2.3 Success Metrics

Metric	Target	Measurement
Developer adoption	50+ developers in year 1	API usage statistics
Integration time	60-80% reduction compared to custom implementation	Developer surveys
Query performance	50ms overhead compared to direct queries	Performance benchmarks
Security compliance	0 critical findings in security audits	Security assessment results
Developer satisfaction	>85% satisfaction rating	Developer feedback surveys
Documentation coverage	100% API coverage with examples	Documentation completeness audit

3. Target Users

3.1 Primary User: Healthcare Application Developer

Based on the Health Tech Developer persona (Alex Chen):

• Developing healthcare applications that need to store PHI securely
• Has technical expertise but limited experience with healthcare compliance
• Values developer experience and productivity
• Seeks to minimize time spent on compliance infrastructure
• Needs clear documentation and examples

3.2 Secondary Users

• Security Officers: Need to verify compliance measures
• Healthcare Administrators: Need to ensure regulatory requirements are met
• Database Administrators: Need to manage the database infrastructure
• Compliance Auditors: Need to verify audit trails and security controls

4. System Architecture

4.1 High-Level Architecture

4.2 Components

Component	Description	Responsibility
DB Wrapper Client	Client library for application integration	Provides API to applications, handles authentication
Query Parser	Analyzes and transforms database queries	Identifies PHI fields, validates permissions
Mutation Handler	Processes data modification operations	Validates data, prepares for encryption
Permission Check	Verifies access rights	Enforces role-based and attribute-based access control
Audit Logger	Records all data access events	Creates immutable audit trail for compliance
Encryption Layer	Handles data encryption	Encrypts PHI fields before storage
Decryption Layer	Handles data decryption	Decrypts PHI fields after retrieval
Result Processor	Post-processes query results	Formats data, applies filtering

4.3 Encryption Strategy

• Field-level encryption using AES-256-GCM
• Key hierarchy with master keys and data encryption keys
• Key rotation capabilities
• Key separation for multi-tenant security

5. Feature Set

5.1 Core Features

Feature	Description	Priority
Field-Level Encryption	Automatic encryption of PHI fields	P0
Transparent Decryption	Automatic decryption when data is accessed	P0
Access Control	Role-based and attribute-based access control	P0
Audit Logging	Comprehensive logging of all data access	P0
PHI Classification	Automatic detection and classification of PHI	P0
Query API	ORM-like API for data queries	P0
Key Management	Secure key storage and rotation	P0
Multi-tenancy	Isolation between different applications/users	P1
Schema Management	Tools for creating and updating schemas	P1
Data Validation	Validation of data formats and types	P1
Bulk Operations	Optimized handling of bulk data operations	P2
Caching Layer	Performance optimization through caching	P2
Data Migration	Tools for migrating existing data	P2

5.2 API Features

Feature	Description	Priority
TypeScript/JavaScript SDK	Client library for Node.js applications	P0
Python SDK	Client library for Python applications	P0
Java SDK	Client library for Java applications	P1
RESTful API	HTTP API for language-agnostic access	P1
GraphQL API	GraphQL interface for flexible queries	P2
CLI Tools	Command-line tools for administration	P2
WebSocket API	Real-time data subscriptions	P3

5.3 Developer Experience Features

Feature	Description	Priority
Interactive Documentation	API documentation with live examples	P0
Sandbox Environment	Test environment with synthetic data	P0
Error Handling	Clear error messages and recovery paths	P0
Logging & Debugging	Developer-friendly logging and troubleshooting	P0
Sample Applications	Example applications demonstrating integration	P1
Migration Guides	Guidance for migrating from other databases	P1
Schema Designer	Visual tool for designing database schemas	P2
Performance Analytics	Tools for analyzing query performance	P2

6. API Design

6.1 Client SDK (TypeScript example)

// Initialize client
import { HealthDB } from "@login-health/db-wrapper";

const db = new HealthDB({
  connectionString: process.env.DATABASE_URL,
  applicationId: process.env.APP_ID,
  applicationSecret: process.env.APP_SECRET,
  encryptionKey: process.env.ENCRYPTION_KEY,
});

// Define a schema (with PHI fields marked)
const patientSchema = db.defineSchema("patient", {
  id: { type: "uuid", primaryKey: true },
  firstName: { type: "string", phi: true },
  lastName: { type: "string", phi: true },
  dateOfBirth: { type: "date", phi: true },
  email: { type: "string", phi: true },
  phoneNumber: { type: "string", phi: true },
  address: { type: "jsonb", phi: true },
  createdAt: { type: "timestamp", default: "now()" },
  updatedAt: { type: "timestamp", default: "now()" },
});

// Create a patient
const patient = await db.patient.create({
  data: {
    firstName: "John",
    lastName: "Doe",
    dateOfBirth: new Date("1980-01-01"),
    email: "john.doe@example.com",
    phoneNumber: "555-123-4567",
    address: {
      street: "123 Main St",
      city: "Anytown",
      state: "CA",
      zipCode: "12345",
    },
  },
});

// Query patients (PHI fields automatically decrypted)
const patients = await db.patient.findMany({
  where: {
    dateOfBirth: {
      gte: new Date("1980-01-01"),
      lt: new Date("1990-01-01"),
    },
  },
  select: {
    id: true,
    firstName: true,
    lastName: true,
    dateOfBirth: true,
  },
});

// Update a patient (PHI fields automatically encrypted)
const updatedPatient = await db.patient.update({
  where: { id: patient.id },
  data: {
    phoneNumber: "555-987-6543",
  },
});

// Access audit log
const auditEvents = await db.auditLog.findMany({
  where: {
    resourceType: "patient",
    resourceId: patient.id,
    action: "update",
  },
  orderBy: {
    timestamp: "desc",
  },
  limit: 10,
});

6.2 API Operations

Operation	Description	Example
defineSchema	Define a database schema with PHI markings	db.defineSchema('patient', {...})
create	Create a new record	db.patient.create({data: {...}})
findUnique	Find a single record by unique identifier	db.patient.findUnique({where: {id: '...'}})
findMany	Find multiple records matching criteria	db.patient.findMany({where: {...}})
update	Update an existing record	db.patient.update({where: {...}, data: {...}})
delete	Delete a record	db.patient.delete({where: {...}})
upsert	Create or update a record	db.patient.upsert({where: {...}, create: {...}, update: {...}})
count	Count records matching criteria	db.patient.count({where: {...}})
aggregate	Perform aggregation operations	db.patient.aggregate({_avg: {age: true}})
transaction	Execute operations in a transaction	db.transaction(async (tx) => {...})

6.3 Access Control

// Define roles and permissions
db.defineRole("doctor", {
  patient: {
    read: true,
    create: true,
    update: true,
    delete: false,
  },
  medicalRecord: {
    read: true,
    create: true,
    update: true,
    delete: false,
  },
});

// Set access rules
db.setAccessRules("patient", {
  read: (user, patient) => {
    // Doctors can read their own patients
    return user.role === "doctor" && patient.doctorId === user.id;
  },
  update: (user, patient) => {
    // Doctors can update their own patients
    return user.role === "doctor" && patient.doctorId === user.id;
  },
});

// Execute query with user context
const patients = await db.patient.findMany({
  where: { doctorId: currentUser.id },
  context: { user: currentUser },
});

6.4 Audit Logging

Every operation is automatically logged with:

• Timestamp
• User identifier
• Action type (create, read, update, delete)
• Resource type (table/collection name)
• Resource identifier
• Changed fields (for updates)
• Access context
• Client information

Audit logs are immutable and stored in a separate table with strict access controls.

7. Development Roadmap

8. Project Epics and User Stories

8.1 Epics

Epic ID	Title	Description	Priority
E1	Foundation Architecture	Establish the core architecture for the HIPAA DB Wrapper	P0
E2	Encryption System	Implement the field-level encryption system	P0
E3	Access Control	Develop the access control framework	P0
E4	Audit Logging	Implement comprehensive audit logging	P0
E5	Core API Development	Build the main query and manipulation API	P0
E6	SDK Development	Create client SDKs for multiple languages	P0
E7	Developer Experience	Enhance developer experience with documentation and tools	P1
E8	Testing & Security	Comprehensive testing and security hardening	P0
E9	Performance Optimization	Optimize performance for production use	P1
E10	Advanced Features	Implement additional features for enhanced functionality	P2

8.2 User Stories by Epic

E1: Foundation Architecture

ID	User Story	Acceptance Criteria	Points
US1.1	As a developer, I want to connect to a PostgreSQL database through the wrapper	• Connection established with proper credentials • Connection pooling implemented • Error handling for connection issues	5
US1.2	As a developer, I want to define database schemas with PHI indicators	• Schema definition API created • PHI field marking supported • Schema validation implemented	8
US1.3	As a system architect, I need a multi-layered security design	• Security architecture documented • Implementation plan created • Security review completed	5
US1.4	As a developer, I want basic CRUD operations working through the wrapper	• Create operation implemented • Read operation implemented • Update operation implemented • Delete operation implemented	13
US1.5	As a developer, I want to initialize the wrapper with configuration options	• Configuration schema defined • Validation of configuration • Sensible defaults provided	3

E2: Encryption System

ID	User Story	Acceptance Criteria	Points
US2.1	As a security officer, I want PHI to be encrypted at rest	• AES-256-GCM encryption implemented • Field-level encryption working • Encryption performance benchmarked	13
US2.2	As a developer, I want encryption to be transparent in queries	• Automatic encryption on write • Automatic decryption on read • Proper error handling for encryption failures	8
US2.3	As a security officer, I want a key hierarchy for enhanced security	• Master key implementation • Data encryption key generation • Key wrapping implementation	13
US2.4	As a system admin, I want to rotate encryption keys periodically	• Key rotation API designed • Data re-encryption process • Minimal downtime during rotation	8
US2.5	As a developer, I want to search on encrypted fields	• Searchable encryption implementation • Query translation for encrypted fields • Performance testing for searches	13

E3: Access Control

ID	User Story	Acceptance Criteria	Points
US3.1	As a security officer, I want role-based access control	• Role definition API • Permission assignment to roles • Role validation during queries	8
US3.2	As a developer, I want to set access rules for different tables	• Table-level access rules • Rule evaluation during queries • Documentation on setting rules	5
US3.3	As a security officer, I want attribute-based access control	• ABAC implementation • Context-aware permission checks • Combining RBAC and ABAC	13
US3.4	As a developer, I want to include user context in queries	• Context parameter in queries • Context validation • Context utilization in access rules	5
US3.5	As a security officer, I want to enforce purpose limitations	• Purpose specification in queries • Purpose validation • Purpose enforcement	8

E4: Audit Logging

ID	User Story	Acceptance Criteria	Points
US4.1	As a compliance officer, I want all data access logged	• Comprehensive audit logging • All CRUD operations logged • User identification in logs	8
US4.2	As a security officer, I want immutable audit logs	• Immutable storage design • Tamper-evident logging • Log verification mechanism	8
US4.3	As a compliance officer, I want to query audit logs	• Audit log query API • Filtering capabilities • Pagination for large logs	5
US4.4	As a developer, I want audit logging to have minimal performance impact	• Performance benchmarking • Asynchronous logging • Log batching for performance	5
US4.5	As a compliance officer, I want detailed information about data changes	• Before/after values recorded • Changed fields identified • Change reason capture	5

E5: Core API Development

ID	User Story	Acceptance Criteria	Points
US5.1	As a developer, I want a familiar query API similar to modern ORMs	• Intuitive query API • Similar patterns to Prisma/Sequelize • Documentation with examples	13
US5.2	As a developer, I want to perform complex queries with filtering	• Complex where clauses • Nested queries • Query optimization	8
US5.3	As a developer, I want to perform transactions across multiple operations	• Transaction API • Rollback capability • Error handling in transactions	8
US5.4	As a developer, I want to use common database operations (join, group, sort)	• Join operations • Grouping and aggregation • Sorting and pagination	13
US5.5	As a developer, I want clear error messages for failed operations	• Detailed error messages • Error categorization • Suggestions for resolution	5

E6: SDK Development

ID	User Story	Acceptance Criteria	Points
US6.1	As a TypeScript developer, I want a type-safe SDK	• TypeScript SDK • Type definitions • Intellisense support	13
US6.2	As a Python developer, I want a Python SDK	• Python SDK • Python idioms followed • Package published to PyPI	13
US6.3	As a developer, I want consistent API patterns across languages	• Consistent naming • Similar method signatures • Shared documentation patterns	5
US6.4	As a developer, I want SDK versioning that follows semver	• Semantic versioning • Changelog maintenance • Backward compatibility policy	3
US6.5	As a developer, I want SDK examples for common operations	• Example code for CRUD • Examples for complex queries • Examples for transactions	5

E7: Developer Experience

ID	User Story	Acceptance Criteria	Points
US7.1	As a developer, I want interactive API documentation	• API documentation site • Interactive examples • Try-it-now functionality	8
US7.2	As a developer, I want a sandbox environment for testing	• Sandbox provision process • Synthetic test data • Reset capability	8
US7.3	As a developer, I want step-by-step integration guides	• Getting started guide • Integration patterns • Troubleshooting guide	5
US7.4	As a developer, I want to validate my implementation	• Compliance checklist • Validation tools • Self-assessment guide	5
US7.5	As a developer, I want example applications demonstrating the wrapper	• Example applications • Best practices demonstrated • Source code available	8

E8: Testing & Security

ID	User Story	Acceptance Criteria	Points
US8.1	As a security officer, I want the wrapper to pass security testing	• OWASP testing • Penetration testing • Vulnerability assessment	13
US8.2	As a quality engineer, I want comprehensive test coverage	• 90%+ code coverage • Unit tests for all components • Integration tests for workflows	13
US8.3	As a developer, I want the wrapper to handle edge cases	• Error handling • Boundary testing • Fault injection testing	8
US8.4	As a security officer, I want security scanning in the CI pipeline	• Automated security scanning • Dependency vulnerability checks • Secret scanning	5
US8.5	As a compliance officer, I want HIPAA requirement validation	• Technical safeguards validated • Documentation for compliance • Gap analysis	8

E9: Performance Optimization

ID	User Story	Acceptance Criteria	Points
US9.1	As a developer, I want minimal query overhead	• 50ms overhead per query • Performance testing methodology • Baseline comparisons	8
US9.2	As a developer, I want efficient handling of large datasets	• Pagination implementation • Streaming large results • Memory optimization	8
US9.3	As a systems engineer, I want connection pooling for throughput	• Connection pool implementation • Pool configuration options • Pool metrics	5
US9.4	As a developer, I want query optimization suggestions	• Query analysis • Performance suggestions • Index recommendations	8
US9.5	As a developer, I want caching options for repeated queries	• Query result caching • Cache invalidation • Cache configuration	8

E10: Advanced Features

ID	User Story	Acceptance Criteria	Points
US10.1	As a developer, I want data versioning capabilities	• Data versioning API • Historical query support • Version comparison	13
US10.2	As a data scientist, I want anonymized data export	• De-identification process • Export functionality • Format options	8
US10.3	As a developer, I want schema migration tools	• Schema migration API • Migration scripts • Rollback capability	13
US10.4	As a developer, I want real-time data subscriptions	• WebSocket API • Subscription management • Event filtering	13
US10.5	As a developer, I want to set data retention policies	• Retention policy API • Automated data archiving • Compliance documentation	8

9. Implementation Considerations

9.1 Technology Stack

Component	Technology	Rationale
Database	PostgreSQL	Strong security features, enterprise-grade, excellent JSON support
Backend Language	TypeScript/Node.js	Strong typing, async performance, large ecosystem
Encryption	AES-256-GCM	Industry standard, authenticated encryption
Key Management	AWS KMS / HashiCorp Vault	Enterprise-grade key security
API Design	REST + TypeScript	Universal compatibility, strong typing
Documentation	OpenAPI + Docusaurus	Industry standard, interactive capabilities
Testing	Jest, SuperTest	Comprehensive testing framework
CI/CD	GitHub Actions	Modern CI/CD, integrates with development workflow

9.2 Performance Considerations

• Query Optimization: Minimize overhead for PHI detection and encryption
• Caching Strategy: Cache frequently accessed non-PHI data
• Indexing Strategy: Special considerations for encrypted fields
• Connection Pooling: Optimize database connections
• Batch Processing: Efficient handling of bulk operations

9.3 Security Considerations

• Key Management: Secure storage and rotation of encryption keys
• Authentication: Integration with Login.Health authentication system
• Authorization: Fine-grained access control
• Audit Trail: Comprehensive, immutable logging
• Vulnerability Management: Regular security assessments

9.4 Compliance Considerations

• HIPAA Technical Safeguards: Encryption, access controls, audit logs
• Documentation: Comprehensive documentation for compliance verification
• Risk Assessment: Regular security and compliance assessments
• Breach Notification: Procedures for identifying and reporting breaches
• BAA Support: Business Associate Agreement considerations

10. Testing Strategy

10.1 Testing Levels

Test Level	Description	Tools	Coverage Target
Unit Testing	Testing individual components	Jest	90% code coverage
Integration Testing	Testing component interactions	Jest + SuperTest	Key workflows
Security Testing	Vulnerability assessment	OWASP ZAP, Snyk	All endpoints
Performance Testing	Measuring performance	k6, autocannon	Key operations
Compliance Testing	Validating HIPAA requirements	Custom checklist	All requirements

10.2 Test Scenarios

1. Encryption/Decryption

   • PHI fields are properly encrypted
   • Non-PHI fields remain unencrypted
   • Decryption works correctly
   • Invalid encryption keys are rejected

2. Access Control

   • Users can only access authorized data
   • Role-based permissions work as expected
   • Attribute-based rules are enforced
   • Unauthorized access attempts are blocked and logged

3. Audit Logging

   • All operations are logged
   • Logs contain all required information
   • Logs are immutable
   • Log queries work correctly

4. API Functionality

   • CRUD operations work as expected
   • Complex queries function correctly
   • Transactions maintain data integrity
   • Error handling works correctly

5. Performance

   • Query overhead is within acceptable limits
   • Large dataset handling is efficient
   • Connection pooling works correctly
   • System handles concurrent operations

11. Documentation Plan

11.1 Documentation Components

Component	Purpose	Format	Audience
API Reference	Detailed API documentation	OpenAPI + Markdown	Developers
Getting Started Guide	Quick start instructions	Markdown + Examples	New developers
Integration Patterns	Common integration approaches	Markdown + Diagrams	Architects
Security Overview	Security architecture	Markdown + Diagrams	Security officers
Compliance Guide	HIPAA compliance details	Markdown + Checklist	Compliance officers
Examples	Example code for common tasks	Code repositories	Developers
Tutorials	Step-by-step instructions	Interactive guides	New developers
FAQ	Common questions and answers	Markdown	All users

11.2 Documentation Approach

• Interactive Documentation: Live API examples
• Code Examples: Multiple languages
• Visual Diagrams: Architecture and flows
• Video Tutorials: Key concepts and implementations
• Regular Updates: Documentation as a living resource

12. Rollout Plan

12.1 Phase 1: Alpha (Week 1-6)

• Internal development
• Architecture implementation
• Basic functionality
• Unit testing

12.2 Phase 2: Beta (Week 7-14)

• Internal developer preview
• Core API implementation
• TypeScript SDK
• Initial documentation
• Security review

12.3 Phase 3: Developer Preview (Week 15-18)

• Limited external developer access
• Complete core functionality
• Python SDK
• Comprehensive documentation
• Performance optimization

12.4 Phase 4: General Availability (Week 19-24)

• Full production launch
• All planned SDKs
• Complete documentation
• Support resources
• Marketing materials

13. Success Criteria and KPIs

13.1 Initial Launch Success Criteria

• HIPAA compliance verified through security assessment
• All P0 features implemented and tested
• Documentation complete for core functionality
• Performance benchmarks meet targets
• Initial developer feedback positive

13.2 Ongoing KPIs

KPI	Target	Measurement
Developer Adoption	50+ active developers in year 1	API usage metrics
Query Performance	50ms average overhead	Performance monitoring
Documentation Satisfaction	>85% satisfaction	Developer surveys
Integration Time	1 week average	Developer feedback
Support Tickets	10 per week	Support system metrics
Security Incidents	0	Security monitoring

14. Team Structure

Role	Responsibilities	Skills Required
Technical Lead	Architecture, technical decisions	Database security, HIPAA, API design
Backend Developer (2)	Core implementation, SDKs	Node.js, PostgreSQL, encryption
Security Engineer	Security implementation, review	Encryption, security architecture
QA Engineer	Testing strategy, implementation	Automated testing, security testing
Documentation Specialist	Developer docs, examples	Technical writing, API documentation
Product Manager	Requirements, roadmap, prioritization	Healthcare experience, developer empathy
DevOps Engineer	CI/CD, infrastructure	AWS, PostgreSQL administration, security

15. Risks and Mitigations

Risk	Impact	Likelihood	Mitigation Strategy
Performance overhead too high	High	Medium	Early performance testing, optimization spikes, architectural adjustments
Encryption complexity impacts usability	High	Medium	Extensive developer testing, intuitive API design, comprehensive examples
Security vulnerabilities	Critical	Low	Regular security reviews, penetration testing, external audit
Compliance gaps	Critical	Low	HIPAA expert consultation, compliance checklist, documentation
Integration challenges with existing systems	Medium	High	Flexible adapter patterns, comprehensive documentation, support resources
Key management complexity	High	Medium	Well-designed key rotation, backup procedures, documentation
Developer adoption barriers	High	Medium	Developer experience focus, excellent documentation, sample applications
Database schema evolution challenges	Medium	Medium	Schema migration tools, versioning support, backward compatibility

16. Dependencies

Dependency	Type	Impact	Management Strategy
PostgreSQL	Technology	Core database	Isolation through abstraction layer, version compatibility testing
Authentication System	Internal	User context for permissions	Well-defined interfaces, mock during development
Encryption Libraries	External	Data security	Evaluated libraries, possible fallback options
Key Management System	External	Key security	Abstraction layer, support for multiple providers
Cloud Infrastructure	External	Hosting environment	Infrastructure as code, environment parity
Regulatory Requirements	External	Compliance	Regular monitoring, compliance expertise

17. Integration Points

17.1 Authentication System

Integration with Login.Health's authentication system to:

• Validate user identity
• Obtain user roles and attributes
• Enforce session management
• Support single sign-on

17.2 Encrypted Datastore

Integration with the core datastore to:

• Manage encryption keys
• Implement field-level encryption
• Maintain audit logs
• Handle data migrations

17.3 Developer Platform

Integration with the Login.Health developer platform to:

• Register applications
• Manage API credentials
• Monitor usage metrics
• Access documentation

17.4 Healthcare Provider Systems

Integration capabilities for healthcare providers to:

• Import existing data
• Normalize data formats
• Map to standard schemas
• Maintain data integrity

18. Glossary

Term	Definition
PHI	Protected Health Information - individually identifiable health information
HIPAA	Health Insurance Portability and Accountability Act
BAA	Business Associate Agreement
Field-Level Encryption	Encrypting individual fields rather than entire database
RBAC	Role-Based Access Control
ABAC	Attribute-Based Access Control
AES-256-GCM	Advanced Encryption Standard with 256-bit key size using Galois/Counter Mode
DEK	Data Encryption Key - key used to encrypt actual data
KEK	Key Encryption Key - key used to encrypt other keys
SDK	Software Development Kit

19. References

1. HIPAA Security Rule - 45 CFR Part 160 and Subparts A and C of Part 164
2. NIST Special Publication 800-66 - Implementing the HIPAA Security Rule
3. OWASP Security Standards for Healthcare Applications
4. PostgreSQL Security Best Practices
5. Field-Level Encryption Implementation Patterns
6. Key Management Best Practices (NIST SP 800-57)
7. Login.Health System Architecture Documentation
8. PostgreSQL JSON/JSONB Documentation

20. Appendices

Appendix A: Detailed Encryption Implementation

// Key hierarchy implementation
class KeyHierarchy {
  // Master key - managed by key management service
  private masterKey: Buffer;

  // Data encryption keys - encrypted with master key
  private dataEncryptionKeys: Map<string, EncryptedKey>;

  constructor(masterKeyIdentifier: string) {
    // Retrieve master key from secure storage
    this.masterKey = KeyManagementService.getKey(masterKeyIdentifier);
    this.dataEncryptionKeys = new Map();
  }

  // Generate a new data encryption key for a tenant
  public generateDataEncryptionKey(tenantId: string): string {
    // Generate random key
    const dek = crypto.randomBytes(32);

    // Encrypt with master key
    const encryptedDek = this.encryptWithMasterKey(dek);

    // Store encrypted DEK
    const keyId = uuid.v4();
    this.dataEncryptionKeys.set(keyId, {
      tenantId,
      encryptedKey: encryptedDek,
      createdAt: new Date(),
    });

    return keyId;
  }

  // Get DEK for encryption/decryption
  public getDataEncryptionKey(keyId: string): Buffer {
    const encryptedKey = this.dataEncryptionKeys.get(keyId);
    if (!encryptedKey) {
      throw new Error(`Key not found: ${keyId}`);
    }

    // Decrypt with master key
    return this.decryptWithMasterKey(encryptedKey.encryptedKey);
  }

  // Encrypt data with a specific DEK
  public encryptField(keyId: string, data: string): EncryptedData {
    const dek = this.getDataEncryptionKey(keyId);

    // Generate initialization vector
    const iv = crypto.randomBytes(16);

    // Create cipher
    const cipher = crypto.createCipheriv("aes-256-gcm", dek, iv);

    // Encrypt
    let encrypted = cipher.update(data, "utf8", "base64");
    encrypted += cipher.final("base64");

    // Get auth tag
    const authTag = cipher.getAuthTag();

    return {
      keyId,
      iv: iv.toString("base64"),
      authTag: authTag.toString("base64"),
      data: encrypted,
    };
  }

  // Decrypt data with a specific DEK
  public decryptField(encryptedData: EncryptedData): string {
    const dek = this.getDataEncryptionKey(encryptedData.keyId);

    // Recreate decipher
    const decipher = crypto.createDecipheriv(
      "aes-256-gcm",
      dek,
      Buffer.from(encryptedData.iv, "base64")
    );

    // Set auth tag
    decipher.setAuthTag(Buffer.from(encryptedData.authTag, "base64"));

    // Decrypt
    let decrypted = decipher.update(encryptedData.data, "base64", "utf8");
    decrypted += decipher.final("utf8");

    return decrypted;
  }

  // Helper methods for master key operations
  private encryptWithMasterKey(data: Buffer): Buffer {
    // Implementation using master key
  }

  private decryptWithMasterKey(data: Buffer): Buffer {
    // Implementation using master key
  }
}

Appendix B: Example Schema Definition

// Example schema definition with PHI marking
const patientSchema = db.defineSchema("patient", {
  id: {
    type: "uuid",
    primaryKey: true,
    defaultValue: "uuid_generate_v4()",
  },
  firstName: {
    type: "string",
    maxLength: 100,
    phi: true,
  },
  lastName: {
    type: "string",
    maxLength: 100,
    phi: true,
  },
  dateOfBirth: {
    type: "date",
    phi: true,
  },
  gender: {
    type: "string",
    enum: ["male", "female", "other", "prefer_not_to_say"],
    phi: true,
  },
  email: {
    type: "string",
    format: "email",
    phi: true,
  },
  phoneNumber: {
    type: "string",
    phi: true,
  },
  address: {
    type: "jsonb",
    phi: true,
    properties: {
      street: { type: "string" },
      city: { type: "string" },
      state: { type: "string" },
      zipCode: { type: "string" },
      country: { type: "string" },
    },
  },
  medicalRecordNumber: {
    type: "string",
    unique: true,
    phi: true,
  },
  insuranceProvider: {
    type: "string",
    phi: false,
  },
  insuranceMemberId: {
    type: "string",
    phi: true,
  },
  primaryCareProviderId: {
    type: "uuid",
    phi: false,
    references: {
      table: "provider",
      column: "id",
    },
  },
  allergies: {
    type: "jsonb",
    phi: true,
    array: true,
  },
  medications: {
    type: "jsonb",
    phi: true,
    array: true,
  },
  createdAt: {
    type: "timestamp",
    defaultValue: "now()",
    phi: false,
  },
  updatedAt: {
    type: "timestamp",
    defaultValue: "now()",
    phi: false,
  },
});

// Define access rules
db.setAccessRules("patient", {
  read: (user, patient) => {
    return (
      // Doctors can read their patients
      (user.role === "doctor" && patient.primaryCareProviderId === user.id) ||
      // Nurses can read patients in their department
      (user.role === "nurse" && user.departmentId === patient.departmentId) ||
      // Admins can read all patients
      user.role === "admin" ||
      // Patients can read their own records
      (user.role === "patient" && patient.id === user.id) ||
      // Surrogates can read their dependents' records
      isSurrogateFor(user.id, patient.id)
    );
  },
  update: (user, patient) => {
    return (
      // Doctors can update their patients
      (user.role === "doctor" && patient.primaryCareProviderId === user.id) ||
      // Admins can update all patients
      user.role === "admin"
    );
  },
  delete: (user, patient) => {
    // Only admins can delete patient records
    return user.role === "admin";
  },
});

Appendix C: Query Examples

// Find patients with a specific condition
const diabeticPatients = await db.patient.findMany({
  where: {
    conditions: {
      arrayContains: {
        code: "E11", // Type 2 Diabetes ICD-10 code
      },
    },
  },
  select: {
    id: true,
    firstName: true,
    lastName: true,
    dateOfBirth: true,
    medications: true,
  },
  context: {
    user: currentUser,
    purpose: "treatment",
  },
});

// Update a patient's contact information
const updatedPatient = await db.patient.update({
  where: {
    id: patientId,
  },
  data: {
    phoneNumber: newPhoneNumber,
    address: {
      street: newStreet,
      city: newCity,
      state: newState,
      zipCode: newZipCode,
    },
  },
  context: {
    user: currentUser,
    purpose: "administrative",
  },
});

// Transaction example
const result = await db.transaction(
  async (tx) => {
    // Create a new patient
    const patient = await tx.patient.create({
      data: {
        firstName: "Jane",
        lastName: "Doe",
        dateOfBirth: new Date("1980-05-15"),
        gender: "female",
        email: "jane.doe@example.com",
      },
    });

    // Create initial medical record
    const medicalRecord = await tx.medicalRecord.create({
      data: {
        patientId: patient.id,
        providerId: currentUser.id,
        visitDate: new Date(),
        diagnosis: "Annual checkup",
        notes: "Patient appears healthy",
      },
    });

    return { patient, medicalRecord };
  },
  {
    context: {
      user: currentUser,
      purpose: "treatment",
    },
  }
);

// Query with complex filtering
const highRiskPatients = await db.patient.findMany({
  where: {
    OR: [
      {
        // Elderly patients with heart conditions
        dateOfBirth: {
          lt: new Date(new Date().setFullYear(new Date().getFullYear() - 65)),
        },
        conditions: {
          arrayContains: {
            code: {
              in: ["I21", "I25", "I50"], // Heart conditions
            },
          },
        },
      },
      {
        // Diabetic patients with kidney issues
        conditions: {
          arrayContains: [
            { code: "E11" }, // Diabetes
            { code: "N18" }, // Chronic kidney disease
          ],
        },
      },
    ],
  },
  include: {
    medications: true,
    recentLabResults: {
      orderBy: { date: "desc" },
      take: 5,
    },
  },
  context: {
    user: currentUser,
    purpose: "population_health",
  },
});